

'''
-- 通知消息表
CREATE TABLE device_notify (

    id SERIAL PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    title VARCHAR(255) NOT NULL,
    content VARCHAR(255) NOT NULL,
    -- 通知类型：1-系统通知，2-设备通知
    type VARCHAR(255) NOT NULL,
    -- 设备ID
    device_id INTEGER NOT NULL,

    CONSTRAINT fk_device_id
        FOREIGN KEY (device_id)
        REFERENCES devices (id)
        ON DELETE CASCADE
);

SELECT SETVAL('notify_id_seq', (SELECT MAX(id) FROM notify));
'''

from . import logger

class DeviceNotifyTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor

    # 创建通知消息
    def create_notify(self, title, content, type, device_id):

        try:
            self.cursor.execute("INSERT INTO device_notify (title, content, type, device_id) VALUES (%s, %s, %s, %s) RETURNING id", (title, content, type, device_id))

        except Exception as e:
            logger.error("Error creating notify: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Notify created successfully.")
            return True, self.cursor.fetchone()["id"]
        
    # 使用设备ID获取通知消息
    def get_notify(self, device_id, limit=5, offset=0):

        try:
            self.cursor.execute("SELECT * FROM device_notify WHERE device_id = %s ORDER BY id DESC LIMIT %s OFFSET %s", (device_id, limit, offset))
            notify = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting notify: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Notify retrieved successfully.")
            return True, notify
        
    def count_notify(self, device_id):
    
        try:
            self.cursor.execute("SELECT COUNT(*) FROM device_notify WHERE device_id = %s", (device_id,))
            count = self.cursor.fetchone()["count"]

        except Exception as e:
            logger.error("Error counting notify: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Notify count retrieved successfully.")
            return True, count
    
    # 获取所有通知消息
    def get_all_notify(self, title=None, content=None, type=None, device_id=None, limit=10, offset=0):
    
        sql = "SELECT * FROM device_notify "
        params = []

        if title:
            sql += " WHERE title = %s"
            params.append(title)

        if content:

            if not params:
                sql += " WHERE content = %s"
            else:
                sql += " AND content = %s"

            params.append(content)

        if type:
    
            if not params:
                sql += " WHERE type = %s"
            else:
                sql += " AND type = %s"

            params.append(type)

        if device_id:
    
            if not params:
                sql += " WHERE device_id = %s"
            else:
                sql += " AND device_id = %s"

            params.append(device_id)

        sql += " ORDER BY id DESC LIMIT %s OFFSET %s"
        params.append(limit)
        params.append(offset)

        try:
            self.cursor.execute(sql, params)
            notify = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting all notify: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"All notify retrieved successfully.")
            return True, notify
        
    def count_all_notify(self, title=None, content=None, type=None, device_id=None):
        
        sql = "SELECT COUNT(*) FROM device_notify "
        params = []

        if title:
            sql += " WHERE title = %s"
            params.append(title)

        if content:
    
            if not params:
                sql += " WHERE content = %s"
            else:
                sql += " AND content = %s"

            params.append(content)

        if type:
        
            if not params:
                sql += " WHERE type = %s"
            else:
                sql += " AND type = %s"

            params.append(type)

        if device_id:
            
            if not params:
                sql += " WHERE device_id = %s"
            else:
                sql += " AND device_id = %s"

            params.append(device_id)

        try:
            self.cursor.execute(sql, params)
            count = self.cursor.fetchone()["count"]

        except Exception as e:
            logger.error("Error counting all notify: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"All notify count retrieved successfully.")
            return True, count
        
    # 删除通知消息
    def delete_notify(self, notify_ids):
        
        try:
            self.cursor.execute("DELETE FROM device_notify WHERE id IN %s", (tuple(notify_ids),))
            self.connection.commit()

        except Exception as e:
            logger.error("Error deleting notify: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Notify {notify_ids} deleted successfully.")
            return True, None

